from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text
from sqlalchemy.orm import backref

app = Flask(__name__)

#  在app.config中设置链接数据库的配置
# SQLAlchemy会自动读取app.config中链接数据库的信息

# MySQL所在的主机名
HOSTNAME = 'localhost'

PORT = '3306'

USERNAME = 'root'

PASSWORD = '123456'

DATABASE = 'oa'

app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT
}/{DATABASE}?charset=utf8mb4"

db = SQLAlchemy(app)

with app.app_context():
    with db.engine.connect() as conn:
        rs = conn.execute(text("select 1"))
        print(rs.fetchone())

# class User(db.Model):
#     __tablename__ = 'user'
#     # primary_key是否为主键
#     id = db.Column(db.Integer, primary_key=True, autoincrement=True)
#     # nullablebu表示不能为空
#     username = db.Column(db.String(100), nullable=False)
#     password = db.Column(db.String(100), nullable=False)
#
#     # 通过user去访问所有的文章
#     # articles = db.relationship('Article', back_populates='article')
# # user = User(username = 'zhangsan', password = '111111')
#
# class Article(db.Model):
#     __tablename__ = 'article'
#     id = db.Column(db.Integer, primary_key=True, autoincrement=True)
#     title = db.Column(db.String(200), nullable=False)
#     content = db.Column(db.Text, nullable=False)
#     #  添加作者的外键
#     author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
#     # 通过user去访问所有的文章
#     # author = db.relationship('User', back_populates='article')
#     # backref会自动给User模型添加一个articles属性用来获取文章列表
#     author = db.relationship('User', backref="articles")
#
#
#
# with app.app_context():
#     db.create_all()
# @app.route('/')
# def hello_world():  # put application's code here
#     return 'Hello World!'
#
#
# @app.route("/user/add")
# def add_user():
#     # 1.创建ORM对象
#     user = User(username="zhangsan",password="111111")
#     # 2.把创建的ORM对象添加db.session中
#     db.session.add(user)
#     # 3.将db.session中的改变同步到数据库中
#     db.session.commit()
#     return "用户创建成功"
#
#
# @app.route("/user/query")
# def query_user():
#     # 1.get查找:根据主键查找
#     # user = User.query.get(1)
#     # print(f"{user.id}:{user.username}:{user.password}")
#     # 2.filter_by查找
#     users = User.query.filter_by(username="zhangsan")
#     for user in users:
#         print(user.username)
#     return "查找成功"
#
# @app.route("/user/update")
# def update_user():
#     user = User.query.filter_by(username="zhangsan").first()
#     user.password = "222222"
#     db.session.commit()
#     return "修改成功"
#
# @app.route("/user/delete")
# def delete_user():
#     # 查找数据
#     user = User.query.get(1)
#     # 从db.session中删除
#     db.session.delete(user)
#     # 3.将db.session中的改变同步到数据库中
#     db.session.commit()
#     return "删除成功"


if __name__ == '__main__':
    app.run()
